ISO 8601 Week Number Stored Procedures
Jonathan Leffler
Here is a shell archive containing the stored procedures
iso8601_weeknum() and iso8601_weekday() - and a supporting stored
procedure day_one_week_one(). The week day code uses the week number
code. Although there are 214 lines in the two files, only 44 of those
are actually code; the rest are comment or blank. With luck, this will
get to the IIUG archives as well as into the Google archives. I must
confess that I was surprised not to find them already in the Google
archives.
: "@(#): shar.sh,v 2.1 1998/06/02 17:13:43 jleffler Exp $"
#! /bin/sh
#
# This is a shell archive.
# Remove everything above this line and run sh on the resulting file.
# If this archive is complete, you will see this message at the end:
# "All files extracted"
#
# Created on: Thu Feb 28 09:04:57 PST 2002
# Created by: jleffler at IBM
#
# Files archived in this archive:
# iso8601_weeknum.spl
# iso8601_weekday.spl
#
#--------------------
if [ -f iso8601_weeknum.spl -a "$1" != "-c" ]
then echo shar: iso8601_weeknum.spl already exists
else
echo 'x - iso8601_weeknum.spl (7230 characters)'
sed -e 's/^X//' >iso8601_weeknum.spl <<'SHAR-EOF'
X-- @(#)$Id: iso8601_weeknum.spl,v 1.1 2001/02/27 20:36:25 jleffler Exp $
X--
X-- Calculate ISO 8601 Week Number for given date
X-- Defines procedures: day_one_week_one() and iso8601_weeknum().
X
X{
XAccording to a summary of the ISO 8601:1988 standard "Data Elements and
XInterchange Formats -- Information Interchange -- Representation of
Xdates and times":
X
X In commercial and industrial applications (delivery times,
X production plans, etc.), especially in Europe, it is often required
X to refer to a week of a year. Week 01 of a year is per definition
X the first week which has the Thursday in this year, which is
X equivalent to the week which contains the fourth day of January. In
X other words, the first week of a new year is the week which has the
X majority of its days in the new year. Week 01 might also contain
X days from the previous year and the week before week 01 of a year is
X the last week (52 or 53) of the previous year even if it contains
X days from the new year. A week starts with Monday (day 1) and ends
X with Sunday (day 7). For example, the first week of the year 1997
X lasts from 1996-12-30 to 1997-01-05 and can be written in standard
X notation as
X
X 1997-W01 or 1997W01
X
X The week notation can also be extended by a number indicating the
X day of the week. For example the day 1996-12-31 which is the
X Tuesday (day 2) of the first week of 1997 can also be written as
X
X 1997-W01-2 or 1997W012
X
X for applications like industrial planning where many things like
X shift rotations are organized per week and knowing the week number
X and the day of the week is more handy than knowing the day of the
X month.
X
XReferring to the standard itself, section 3.17 defines a calendar week:
X
X week, calendar: A seven day period within a calendar year, starting
X on a Monday and identified by its ordinal number within the year;
X the first calendar week of the year is the one that includes the
X first Thursday of that year. In the Gregorian calendar, this is
X equivalent to the week which includes 4 January.
X
XSection 5.2.3 "Date identified by Calendar week and day numbers" states:
X
X Calendar week is represented by two numeric digits. The first
X calendar week of a year shall be identified as 01 [...]
X
X Day of the week is represented by one decimal digit. Monday
X shall be identified as day 1 of any calendar week [...]
X
XSection 5.2.3.1 "Complete representation" states:
X
X When the application clearly identifies the need for a complete
X representation of a date identified by calendar week and day
X numbers, it shall be one of the alphanumeric representations as
X follows, where CCYY represents a calendar year, W is the week
X designator, ww represents the ordinal number of a calendar week
X within the year, and D represents the ordinal number within the
X calendar week.
X
X Basic format: CCYYWwwD
X Example: 1985W155
X Extended format: CCYY-Www-D
X Example: 1985-W15-5
X
XBoth the summary and the formal definition are intuitively clear, but it
Xis not obvious how to translate it into an algorithm. However, we can
Xdeal with the problem by exhaustively enumerating the seven options for
Xthe day of the week on which 1st January falls (with actual year values
Xfor concreteness):
X
X 1st January 2001 is Monday => Week 1 starts on 2001-01-01
X 1st January 2002 is Tuesday => Week 1 starts on 2001-12-31
X 1st January 2003 is Wednesday => Week 1 starts on 2002-12-30
X 1st January 2004 is Thursday => Week 1 starts on 2003-12-29
X 1st January 2010 is Friday => Week 1 starts on 2010-01-04
X 1st January 2005 is Saturday => Week 1 starts on 2005-01-03
X 1st January 2006 is Sunday => Week 1 starts on 2006-01-02
X
X(Cross-check: 1st January 1997 was a Wednesday; the summary notes state
Xthat week 1 of 1997 started on 1996-12-30, which is consistent with the
Xtable derived for dates in the first decade of the third millennium
Xabove).
X
XWhen working with the Informix DATE types, bear in mind that Informix
Xuses WEEKDAY values 0 = Sunday, 1 = Monday, 6 = Saturday. When the
Xweekday of the first of January has the value in the LH column, you need
Xto add the value in the RH column to the 1st of January to obtain the
Xdate of the first day of the first week of the year.
X
X Weekday Offset to
X 1st January 1st day of week 1
X
X 0 +1
X 1 0
X 2 -1
X 3 -2
X 4 -3
X 5 +3
X 6 +2
X
XThis can be written as MOD(11-w,7)-3 where w is the (Informix encoding
Xof the) weekday of 1st January and the value 11 is used to ensure that
Xno negative values are presented to the MOD operator. Hence, the
Xexpression for the date corresponding to the 1st day (Monday) of the 1st
Xweek of a given year, yyyy, is:
X
X d1w1 = MDY(1, 1, yyyy) + MOD(11 - WEEKDAY(MDY(1,1,yyyy)), 7) - 3
X
XThis expression is encapsulated in stored procedure day_one_week_one:
X}
X
XCREATE PROCEDURE day_one_week_one(yyyy INTEGER) RETURNING DATE;
X DEFINE jan1 DATE;
X LET jan1 = MDY(1, 1, yyyy);
X RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3;
XEND PROCEDURE;
X
X{
XGiven this date d1w1, we can calculate the week number of any other date
Xin the same year as:
X
X TRUNC((dateval - d1w1) / 7) + 1
X
XThe residual issues are ensuring that the wraparounds are correct. If
Xthe given date is earlier than the start of the first week of the year
Xthat contains it, then the date belongs to the last week of the previous
Xyear. If the given date is on or after the start of the first week of
Xthe next year, then the date belongs to the first week of the next year.
X
XGiven these observations, we can write iso8601_weeknum as shown below.
X(Beware: iso8601_week_number() is too long for servers with the
X18-character limit; so is day_one_of_week_one()).
X
XThen comes the interesting testing phase -- when do you get week 53?
XOne answer is on Friday 1st January 2010, which is in 2009-W53 (as,
Xindeed, is Sunday 3rd January 2010). Similarly, Saturday 1st January
X2005 is in 2004-W53, but Sunday 1st January 2006 is in 2005-W52.
X}
X
XCREATE PROCEDURE iso8601_weeknum(dateval DATE DEFAULT TODAY) RETURNING CHAR(8);
X DEFINE rv CHAR(8);
X DEFINE yyyy CHAR(4);
X DEFINE ww CHAR(2);
X DEFINE d1w1 DATE;
X DEFINE tv DATE;
X DEFINE wn INTEGER;
X DEFINE yn INTEGER;
X -- Calculate year and week number.
X LET yn = YEAR(dateval);
X LET d1w1 = day_one_week_one(yn);
X IF dateval < d1w1 THEN
X -- Date is in early January and is in last week of prior year
X LET yn = yn - 1;
X LET d1w1 = day_one_week_one(yn);
X ELSE
X LET tv = day_one_week_one(yn + 1);
X IF dateval >= tv THEN
X -- Date is in late December and is in the first week of next year
X LET yn = yn + 1;
X LET d1w1 = tv;
X END IF;
X END IF;
X LET wn = TRUNC((dateval - d1w1) / 7) + 1;
X -- Calculation complete: yn is year number and wn is week number.
X -- Format result.
X LET yyyy = yn;
X IF wn < 10 THEN
X LET ww = '0' || wn;
X ELSE
X LET ww = wn;
X END IF
X LET rv = yyyy || '-W' || ww;
X RETURN rv;
XEND PROCEDURE;
SHAR-EOF
chmod 400 iso8601_weeknum.spl
if [ `wc -c <iso8601_weeknum.spl` -ne 7230 ]
then echo shar: iso8601_weeknum.spl unpacked with wrong size
fi
# end of overwriting check
fi
#--------------------
if [ -f iso8601_weekday.spl -a "$1" != "-c" ]
then echo shar: iso8601_weekday.spl already exists
else
echo 'x - iso8601_weekday.spl (1290 characters)'
sed -e 's/^X//' >iso8601_weekday.spl <<'SHAR-EOF'
X-- @(#)$Id: iso8601_weekday.spl,v 1.1 2001/04/03 19:34:43 jleffler Exp $
X--
X-- Calculate ISO 8601 Week Number for given date
X-- Defines procedure: iso8601_weekday().
X-- Uses procedure: iso8601_weeknum().
X
X{
XAccording to a summary of the ISO 8601:1988 standard "Data Elements and
XInterchange Formats -- Information Interchange -- Representation of
Xdates and times":
X
X The week notation can also be extended by a number indicating the
X day of the week. For example the day 1996-12-31 which is the
X Tuesday (day 2) of the first week of 1997 can also be written as
X
X 1997-W01-2 or 1997W012
X
X for applications like industrial planning where many things like
X shift rotations are organized per week and knowing the week number
X and the day of the week is more handy than knowing the day of the
X month.
X
XThis procedure uses iso8601_weeknum() to format the YYYY-Www part of the
Xdate, and appends '-d' to the result, allowing for Informix's coding of
XSunday as day 0 rather than day 7 as required by ISO 8601.
X}
X
XCREATE PROCEDURE iso8601_weekday(dateval DATE DEFAULT TODAY) RETURNING CHAR(10);
X DEFINE rv CHAR(10);
X DEFINE dw CHAR(4);
X LET dw = WEEKDAY(dateval);
X IF dw = 0 THEN
X LET dw = 7;
X END IF;
X RETURN iso8601_weeknum(dateval) || '-' || dw;
XEND PROCEDURE;
SHAR-EOF
chmod 400 iso8601_weekday.spl
if [ `wc -c <iso8601_weekday.spl` -ne 1290 ]
then echo shar: iso8601_weekday.spl unpacked with wrong size
fi
# end of overwriting check
fi
echo All files extracted
exit 0
--
Jonathan Leffler #include <disclaimer.h>
STSM, Informix Database Engineering, IBM Data Management Solutions
Phone: +1 650-926-6921 Tie-line: 630-6921
Email: jlef...@us.ibm.com (RIP jlef...@informix.com)
Notes ID: Jonathan Leffler/Menlo Park/IBM@IBMUS
Guardian of DBD::Informix v1.00.PC2 -- http://dbi.perl.org
*=*=*=*=*=* THE END IS NIGH! *=*=*=*=*=*
Please use jlef...@us.ibm.com because jlef...@informix.com will
not work from 2002-07-01. Expect slower responses from 2002-02-18
because email will be sent to Notes and I can't use Lotus Notes as
fast as Unix email. One day, this signature will shrink!